from selenium import webdriver
from selenium.webdriver.common.by import By
import time 
import pandas as pd
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait
import json
from selenium.webdriver.common.action_chains import ActionChains
import os
from copy import deepcopy
import pyautogui
import pymysql
import pandas as pd
from icecream import ic
import win32api
import win32gui
from win32con import WM_INPUTLANGCHANGEREQUEST
import requests

def change_language(lang="EN"):
    """
    切换语言
    :param lang: EN––English; ZH––Chinese
    :return: bool
    """
    LANG = {
        "ZH": 0x0804,
        "EN": 0x0409
    }
    hwnd = win32gui.GetForegroundWindow()
    language = LANG[lang]
    result = win32api.SendMessage(
        hwnd,
        WM_INPUTLANGCHANGEREQUEST,
        0,
        language
    )
    if not result:
        return True

def out(xpath):
    html = driver.find_element(By.XPATH, xpath).get_attribute("outerHTML")
    with open('out.html','w', encoding='utf8') as wf:
        wf.write(html)

def sleep():
    time.sleep(0.5)

def shortsleep():
    time.sleep(0)

def longsleep():
    time.sleep(5)

def click(xpath):
    # button = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    button = driver.find_element(By.XPATH, xpath)
    button.click()
    shortsleep()

def exist(xpath):
    try:
        driver.find_element(By.XPATH, xpath)
    except:
        return False
    else:
        return True

def write(xpath, text):
    inputTag = driver.find_element(By.XPATH, xpath)
    # inputTag = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # inputTag = driver.find_element(By.XPATH, xpath)
    inputTag.clear()
    inputTag.send_keys(text)
    shortsleep()

def get_html(xpath):
    # # item = driver.find_element(By.XPATH, xpath)
    # item = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # # return item.text
    # # return item.get_attribute('textContent')
    html = driver.find_element(By.XPATH, xpath).get_attribute("outerHTML")
    return html

def get_value(xpath):
    html = driver.find_element(By.XPATH, xpath).get_attribute("value")
    return html

def get_text(xpath):
    # item = driver.find_element(By.XPATH, xpath)
    # item = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # return item.text
    # return item.get_attribute('textContent')
    text = driver.find_element(By.XPATH, xpath).text
    return text

def switch(xpath):
    iframe = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, xpath)))
    # iframe = driver.find_element(By.XPATH, xpath)
    driver.switch_to.frame(iframe)
    shortsleep()

def exist_dt(dt_name):
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[4]/a") ## 点击‘资源维护’
    ## 切换iframe
    switch('//*[@id="frame"]')
    ## 输入查询内容
    write('html/body/div[1]/section/div/form/div[1]/input', dt_name)
    ## 点击库表
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")
    ## 点击查询
    click("/html/body/div[1]/section/div/form/div[6]/button[1]")
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')
    def match():
        if '表中无数据存在' in x:
            return False
        for j in range(x.count('<tr')):
            if get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(j+1))==dt_name:
            # and get_text('//*[@id="list-table"]/tbody/tr[{}]/td[3]'.format(j+1))=='宁德市信息中心':
                return True
        return False
    return match()

def auto_dt_register(system_name, catalog, db_name, shujuku, shujubiao):
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content()
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[3]/a') #点击
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[3]/ul/li[1]/a') # 点击
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe') # 转换
    write('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[1]/div/input', db_name) #  填入资源名称
    sleep()
    click('//*[@id="selectFromSystemDiv"]/span')#  点击选择系统
    sleep()
    write('/html/body/div[6]/div/div/div[2]/div[1]/div/div/div[1]/div/input', system_name) # 写入 系统名称
    click('/html/body/div[6]/div/div/div[2]/div[1]/div/div/div[2]/button[1]') # 点击查询
    sleep()
    click('/html/body/div[6]/div/div/div[2]/div[2]/div/div/div[1]/table/tbody/tr/td[1]/input') # 点击查到的内容
    click('/html/body/div[6]/div/div/div[3]/button[1]') # 点击确定
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[3]/div/div/span/button') # 点击选择目录
    write('/html/body/div[4]/div/div/div[2]/div[1]/div/input[3]', catalog) # 写入目录名称
    click('/html/body/div[4]/div/div/div[2]/div[1]/div/button[1]') # 点击查询
    sleep()
    sleep()
    while not exist('//span[text()="{}"]'.format(catalog)):
        click('//*[@id="catalogs-table_next"]')
        sleep()
    click('//span[text()="{}"]'.format(catalog)) #点击查到的内容
    # click('//*[@id="catalogs-table"]/tbody/tr[1]/td[2]/span')#=============================================================================================================================================
    click('/html/body/div[4]/div/div/div[3]/button[1]') #点击确定
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[5]/div[1]/select') #点击数据更新周期
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[5]/div[1]/select/option[6]') #选择每半年
    write('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[12]/div/textarea',db_name) #写入库表描述
    click('/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[2]/a')  #点击下一步
    sleep()
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[1]/div/div/span/button') #点击选择数据表
    sleep()
    write('/html/body/div[2]/div/div/div[2]/div/div/div[1]/input[1]', shujuku) #写入 接口库表数据库
    click('/html/body/div[2]/div/div/div[2]/div/div/div[1]/button') #点击查询
    sleep()
    if shujuku == '信用库':
        click('/html/body/div[2]/div/div/div[2]/div/div/div[1]/div/div[1]/table/tbody/tr[2]/td[1]/input') #选择查到的内容
    else:
        click('/html/body/div[2]/div/div/div[2]/div/div/div[1]/div/div[1]/table/tbody/tr[1]/td[1]/input') #选择查到的内容
    write('/html/body/div[2]/div/div/div[2]/div/div/div[2]/input', shujubiao) #写入表名
    click('/html/body/div[2]/div/div/div[2]/div/div/div[2]/button') #点击查询
    sleep()
    while not exist('//input[@value="{}"]'.format(shujubiao.lower())):
        click('//*[@id="dataTableTable_next"]')
        sleep()
    click('//input[@value="{}"]'.format(shujubiao.lower())) #选择查到的内容
    click('/html/body/div[2]/div/div/div[3]/button[2]') #点击确定
    write('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[6]/div/div/input','500') #写入 500
    click('/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[4]/a') #点击保存并提交
    driver.switch_to.default_content() #转到主页面
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/a') #点击资源审核
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/ul/li[1]/a') #点击资源注册审核
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe') #转到frame
    write('/html/body/div[1]/section/div/form/div[1]/input',db_name) #写入库表名称
    # click('/html/body/div[1]/section/div/form/div[4]/select') #点击资源类型
    click('/html/body/div[1]/section/div/form/div[4]/select/option[2]') #点击库表
    click('/html/body/div[1]/section/div/form/div[5]/button[1]') #点击查询
    sleep()
    click('/html/body/div[1]/section/div/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') #点击审核
    sleep()
    click('/html/body/div/section/div/div/div[2]/div[1]/form/div/div/button[1]') #点击审核通过
    click('/html/body/div[3]/div[3]/a[1]') #点击确定
    sleep()
    sleep()
    driver.switch_to.default_content() #切换主页面
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[6]/a') #点击资源发布
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe') #切换frame
    write('/html/body/div[1]/section/div/form/div[1]/input',db_name) #输入资源名称
    click('/html/body/div[1]/section/div/form/div[4]/select/option[2]') #点击库表
    click('//button[@id="search_resource"]') #点击查询
    sleep()
    click('/html/body/div[1]/section/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') #点击发布
    driver.switch_to.default_content() #切换主页面
    click('//a[text()="确定"]') #点击确定

def login_resource():
    driver.maximize_window()
    # 打开页面
    driver.get('http://10.64.202.213/resource')
    # 填入用户名和密码
    write('/html/body/div/section/div/div[1]/form/div[1]/input', 'platform_admin')
    write('/html/body/div/section/div/div[1]/form/div[2]/input', '!QAZ2wsx')
    # 手动输入验证码
    change_language()
    click('/html/body/div/section/div/div[1]/form/div[3]/div/div/input')
    longsleep()
    # 点击登录
    click('/html/body/div/section/div/div[1]/form/button')

def exist_interface(interface_name):
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
    click('//*[@id="nav-side"]/li[8]/ul/li[2]') # 点击数据服务维护
    switch('//*[@id="frame"]') # 切换frame
    write('//*[@id="search_resource_title_code"]',interface_name)  #填入资源名称
    click('//*[@id="search_resource"]') # 点击查询
    sleep()
    x = get_html('//*[@id="list-table"]/tbody') # 获取内容
    def match():
        if '表中无数据存在' in x:
            return False
        for i in range(1, x.count('<tr')+1):
            # 匹配接口名
            if interface_name == get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)):
                return True
        return False
    return match()

def auto_interface_register(system_name, catalog_name, dt_name, table_name, interface_index, interface_name, interface_name_en):
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
    # /html/body/div[3]/div[1]/div/div[2]/ul/li[8]
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]/ul/li[1]') # 点击数据服务注册
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe') # 切换frame
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[1]/div/div/span/button') # 点击选择库表资源
    write('/html/body/div[6]/div/div/div[2]/div[1]/div/input',dt_name) # 填入 dbname
    click('/html/body/div[6]/div/div/div[2]/div[1]/div/button[1]') # 点击查询
    sleep()
    sleep()
    # click('//td[text()="{}"]/parent::tr/td[1]/input'.format(dt_name))# 点击目标库表
    # print(get_html())
    if not exist('//span[@title="{}"]/../../../td[1]/input'.format(dt_name)):
        dt_name = dt_name+'新'
        if not exist('//span[@title="{}"]/../../../td[1]/input'.format(dt_name)):
            return '库表不存在'
    click('//span[@title="{}"]/../../../td[1]/input'.format(dt_name))# 点击目标库表
    click('/html/body/div[6]/div/div/div[3]/button[1]') # 点击确定。
    write('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[2]/div/input', interface_name)  # 写入 库表名+信息查询接口
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[3]/div/div/span/button') # 点击选择系统
    sleep()
    write('/html/body/div[5]/div/div/div[2]/div[1]/div/div/div[1]/div/input', system_name) # 写入系统名
    click('/html/body/div[5]/div/div/div[2]/div[1]/div/div/div[2]/button[1]') # 点击查询
    sleep()
    click('//input[@data-name="{}"]'.format(system_name))     # 点击目标系统 
    click('/html/body/div[5]/div/div/div[3]/button[1]') # 点击确定
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[4]/div/div/span/button') # 点击选择目录
    write('/html/body/div[4]/div/div/div[2]/div[1]/div/input[3]', catalog_name) # 写入目录名称
    click('/html/body/div[4]/div/div/div[2]/div[1]/div/button[1]') # 点击查询
    sleep()
    while not exist('//span[@value="{}"]'.format(catalog_name)):
        click('//*[@id="catalogs-table_next"]') #点击下一页
        sleep()
    click('//span[@value="{}"]'.format(catalog_name))   # 点击目标目录
    click('/html/body/div[4]/div/div/div[3]/button[1]')# 点击确定
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[6]/div[1]/select/option[6]') # 点击每半年数据更新
    write('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[15]/div/textarea', interface_name) # 写入库表名+信息查询接口
    click('/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[2]/a') # 点击下一步
    sleep()
    write('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[1]/div/input','/'+interface_name_en) # 写入/表名
    sleep()
    click('//*[@id="steps-uid-0-p-1"]/div/div[1]/label') # 点击标签
    sleep()
    if exist('//*[@id="servicePath-error"]'):
        print('地址重复')
        return '地址重复'
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[2]/div/div[2]/button') # 点击添加参数
    sleep()
    if not exist('//option[@value="{}"]'.format(interface_index)):
        return '索引不存在'
    click('//option[@value="{}"]'.format(interface_index))  # 选择ID名
    write('/html/body/div[2]/div/div/div[2]/div/div[5]/div/input', interface_index) # 写入    TTTTTT_TID
    click('/html/body/div[2]/div/div/div[3]/button[1]') # 点击确定
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[3]/div/div/textarea[2]')  # 点击服务逻辑
    sleep()
    sleep()
    click('/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div/span[1]') # 点击添加条件
    click('/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[1]/select/option[@value="{}"]'.format(interface_index)) #选择项目
    click('/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[2]/select/option[@value="="]') # 选择等于
    write('/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[3]/input',interface_index)
    # click('/html/body/div[3]/div/div/div[2]/div/div/div/div/div/div[2]/div[1]/div[3]/ul/li/a')  # 点击元素
    click('/html/body/div[3]/div/div/div[3]/button[1]') # 点击确定
    click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[2]/div/div[4]/div/div[2]/button') #点击全选
    click('/html/body/div[1]/div/div/section/div/form/div/div[3]/ul/li[4]/a') # 点击保存并提交
    driver.switch_to.default_content() #切换主页面
    sleep()
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]/ul/li[3]') #点击数据服务审核
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]/ul/li[3]/ul/li[1]') # 点击注册审核
    sleep()
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe') # 切换frame
    # write('/html/body/div[1]/section/div/form/div[1]/input',db_name+'信息查询接口') # 写入dbname
    # click('//*[@id="search_resource"]') # 点击查询
    # click('/html/body/div[1]/section/div/form/div[5]/button[1]')# 点击查询
    # element = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, '/html/body/div[1]/section/div/form/div[5]/button[1]')))
    # element.click()
    click('/html/body/div[1]/section/div/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') # 点击审核
    click('/html/body/div/section/div/div/div[2]/div[1]/form/div/div/button[1]') # 点击审核通过
    click('/html/body/div[3]/div[3]/a[1]') # 点击确定
    return '接口已存在'

def test_login():
    # 打开页面
    driver.get('http://10.64.202.213/manage')
    # driver.execute_script("document.body.style.zoom='0.8'")
    # 填入用户名和密码
    write('/html/body/div/section/div/div[1]/form/div[1]/input', 'platform_admin')
    write('/html/body/div/section/div/div[1]/form/div[2]/input', '!QAZ2wsx')
    # 手动输入验证码
    click('/html/body/div/section/div/div[1]/form/div[3]/div/div/input')
    longsleep()
    # 点击登录
    click('/html/body/div/section/div/div[1]/form/button')



def auto_test(interface_name, interface_par):
    driver.get('http://10.64.202.213/manage')
    driver.switch_to.default_content() #切换主页面
    click('/html/body/div[1]/div/div[3]/div[2]/ul/li[2]') # 点击接口注册
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe')  # 切换frame
    write('/html/body/div/section/div/div/div/div[1]/form/div[1]/input', '') # 填入接口名
    write('/html/body/div/section/div/div/div/div[1]/form/div[1]/input', interface_name) # 填入接口名
    click('/html/body/div/section/div/div/div/div[1]/form/div[2]/button[1]') # 点击查询
    sleep()
    x = get_html('//*[@id="service-test-table"]/tbody')
    if '表中无数据存在' in x:
        return '待测试状态中无此接口', ''
    ind = 0
    for i in range(1, x.count('<tr')+1):
        if interface_name == get_text('//*[@id="service-test-table"]/tbody/tr[{}]/td[2]'.format(i)):
            ind = i
            break
    click('/html/body/div/section/div/div/div/div[1]/div/div/div[1]/table/tbody/tr[{}]/td[5]/a'.format(ind)) # 点击测试
    click('/html/body/div/section/div/div[2]/form/div[1]/div[1]/div/a') # 点击测试，此时开新标签页
    sleep()
    # 切换标签页
    n = driver.window_handles  # 这个时候会生成一个新窗口或新标签页的句柄，代表这个窗口的模拟driver
    driver.switch_to.window(n[-1])  # driver切换至最新生产的页面
    if exist('//div[text()=" 403 "]'):
        return '跳过','403'
    write('/html/body/section/div/form/div[4]/div/table/tbody/tr/td[2]/input', interface_par) # 写入测试数据
    click('/html/body/section/div/form/div[5]/div/button[1]') # 点击发送请求
    result = get_value('/html/body/section/div/form/div[6]/div/textarea')  # 获取输出内容
    i = 0
    while not result:
        result = get_value('/html/body/section/div/form/div[6]/div/textarea')
        i+=1
        sleep()
    driver.close()
    driver.switch_to.window(n[0])
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe')  # 切换frame
    # 检查输出内容
    x = json.loads(result)
    if x['code'] and x['data'] and interface_par in result:
        driver.execute_script("window.scrollTo(0, document.body.scrollHeight);") # 下拉到页面底部
        write('/html/body/div/section/div/div[2]/form/div[1]/div[2]/div/textarea', result[:400])
        click('/html/body/div/section/div/div[2]/form/div[2]/div/button[1]')# 点击通过
        driver.switch_to.default_content() #切换主页面
        click('//div[@id="layui-layer1"]/div[3]/a[1]') # 点击确认
        while(len(x['data'])>1):
            del x['data'][1]
        return '测试通过', json.dumps(x)
    else:
        return '测试不通过', result


def interface_test():
    # 登录系统
    test_login()
    df = pd.read_excel('fengong1.xlsx')
    for i in range(df.shape[0]):
        if pd.isnull(df.iloc[i]['测试情况']):
            re = auto_test(df.iloc[i]['库表']+'信息查询接口', df.iloc[i]['测试用例'])
            if re:
                print(df.iloc[i]['库表'],'完成')
                df.iloc[i]['测试情况'] = re
                print(re)
                df.to_excel('{}.xlsx'.format(i))
    # service__register('宁德市政务大数据平台', '宁德市资源共享服务平台', '城镇单位在岗职工平均工资年均增长速度', 'czdwzgzzpjgznjzzsd')
    # driver.quit()
    # quit()
    df.to_excel('qqqq.xlsx')
    
def resource():
    # driver = webdriver.Chrome() 
    resource_login()
    # # 读取数据
    # df = pd.read_excel('fengong1.xlsx')
    # for i in range(df.shape[0]):
    #     print()
    #     if pd.isnull(df.iloc[i]['注册接口情况']):
    #         service_register('宁德市政务大数据平台', '宁德市资源共享服务平台', df.iloc[i]['库表'], df.iloc[i]['接口'])
    #         # df.iloc[i]['库表注册情况']='注册库表完成'
    #         # df.to_excel('fengong1.xlsx')
    #         print(df.iloc[i]['库表'],'完成')
    # # service__register('宁德市政务大数据平台', '宁德市资源共享服务平台', '城镇单位在岗职工平均工资年均增长速度', 'czdwzgzzpjgznjzzsd')
    # driver.quit()
    # quit()

def login_submit():
    driver.get('http://10.64.202.219:8081') # 打开页面
    write('/html/body/form/div/div[2]/div[1]/div[1]/input', 'ndszb_lbf') # 填入用户名
    write('/html/body/form/div/div[2]/div[1]/div[2]/input', 'lbf@2021') # 填入密码
    # 手动输入验证码
    # click('/html/body/div/section/div/div[1]/form/div[3]/div/div/input')
    # longsleep()
    # 点击登录
    click('/html/body/form/div/div[2]/div[1]/input')

def login_bsp():
    driver.get('http://10.64.202.213/bsp') # 打开页面
    write('/html/body/div[2]/section/div[1]/div[1]/form/div[1]/input[1]', 'platform_admin') # 填入用户名
    write('/html/body/div[2]/section/div[1]/div[1]/form/div[2]/input', '!QAZ2wsx') # 填入密码
    # 手动输入验证码
    click('/html/body/div[2]/section/div[1]/div[1]/form/div[3]/div/div/input')
    # longsleep()
    # 点击登录
    click('/html/body/div[2]/section/div[1]/div[1]/form/button')

def auto_submit(guishubumen, interface_name, return_value, parameter_name, parameter_value, interface_address, biaoming, suoyinshuoming, chucanmiaoshu):
    '''执行一条自动提交动作
    '''
    login_submit()
    sleep()
    sleep()
    click('/html/body/div[2]/div[1]/ul/li[7]/a')# 点击接口管理
    switch('/html/body/div[2]/div[2]/iframe') # 切换iframe
    click('/html/body/div[2]/div[1]/a[2]') # 点击地市上报接口
    write('/html/body/div[2]/div[2]/input[2]', interface_name) # 写入服务名称
    click('/html/body/div[2]/div[2]/input[3]') # 点击搜索
    sleep()
    if exist('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]'): # 可以搜索到
        click('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]') # 点击查看详情
        sleep()
        switch('//*[@id="layui-layer-iframe1"]')
        sleep()
        click('//option[text()="{}"]'.format(guishubumen)) # 点击机构名称
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[2]/input', interface_name) # 写入接口名称
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[3]/input', interface_address) #写入接口地址
        click('//*[@id="typeselect"]/option[1]') #点击接口类型
        click('/html/body/div[1]/div/div[1]/table/tbody/tr[2]/td[2]/span/select/option[2]') # 点击请求类型get
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[3]/td/input', biaoming) # 写入查询业务表
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[4]/td/textarea', interface_name) # 写入接口描述
        x = json.loads(return_value)
        while(len(x['data'])>1):
            del x['data'][1]
        while(len(x['data'][0])>50):
            data_ = list(x['data'][0].keys())[0]
            del x['data'][0][data_]
        for k in x['data'][0].keys():
            if isinstance(x['data'][0][k],str):
                x['data'][0][k] = x['data'][0][k][:20].replace('"','')
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[5]/td/textarea', json.dumps(x, ensure_ascii=False)) # 更改返回值
        write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[2]/input', parameter_name) # 写入参数名称
        write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[4]/input',suoyinshuoming) # 写入参数描述
        write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[8]/input', parameter_value) # 写入请求示例
        z = json.loads(return_value)
        for k in z['data'][0].keys():
            if isinstance(z['data'][0][k],str):
                z['data'][0][k] = z['data'][0][k][:20].replace('"','')
        z = z['data'][0]
        y = json.loads(chucanmiaoshu)
        for i in range(get_html('//*[@id="outtable"]/tbody').count('<tr')):
            # click('/html/body/div[1]/div/h2[2]/a') # 点击添加输出参数
            write('//*[@id="outtable"]/tbody/tr[{}]/td[2]/input'.format(i+1), "par"+str(i))
            write('//*[@id="outtable"]/tbody/tr[{}]/td[4]/input'.format(i+1), "par"+str(i))
            write('//*[@id="outtable"]/tbody/tr[{}]/td[6]/input'.format(i+1), "par"+str(i))
        driver.execute_script("window.scrollBy(0,10)") # 下拉页面
        click('//*[@id="savebtn"]') #点击更新
        WebDriverWait(driver, 10).until(EC.alert_is_present())
        x = driver.switch_to.alert
        sleep()
        sleep()
        x.accept()
        sleep()
        driver.switch_to.default_content() #切换主页面
        click('/html/body/div[2]/div[1]/ul/li[7]/a')# 点击接口管理
        switch('/html/body/div[2]/div[2]/iframe') # 切换iframe
        click('/html/body/div[2]/div[1]/a[2]') # 点击地市上报接口
        write('/html/body/div[2]/div[2]/input[2]', interface_name) # 写入服务名称
        click('/html/body/div[2]/div[2]/input[3]') # 点击搜索
        click('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[2]') # 点击删除
        sleep()
        sleep()
        sleep()
        x = driver.switch_to.alert
        sleep()
        re = x.text
        sleep()
        x.accept()
        sleep()
        sleep()
        x = driver.switch_to.alert
        sleep()
        re = x.text
        sleep()
        x.accept()
        print(re)
        if re == '删除失败！接口不存在':
            click('/html/body/div[2]/div[2]/input[4]') # 点击服务上报
            click('//a[contains(string(), "HttpRestFul")]') # 点击http
            sleep()
            driver.switch_to.default_content() #切换主页面
            switch('//*[@id="iframepage"]')
            sleep()
            iframe = driver.find_element(By.XPATH, '//*[@id="layui-layer-iframe2"]')
            driver.switch_to.frame(iframe)
            click('//option[text()="{}"]'.format(guishubumen)) # 点击机构名称
            write('/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[2]/input', interface_name) # 写入接口名称
            write('/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[3]/input', interface_address) #写入接口地址
            click('//*[@id="typeselect"]/option[1]') #点击接口类型
            click('/html/body/div[1]/div/div[1]/table/tbody/tr[2]/td[2]/span/select/option[2]') # 点击请求类型get
            write('/html/body/div[1]/div/div[1]/table/tbody/tr[3]/td/input', biaoming) # 写入查询业务表
            write('/html/body/div[1]/div/div[1]/table/tbody/tr[4]/td/textarea', interface_name) # 写入接口描述
            x = json.loads(return_value)
            while(len(x['data'])>1):
                del x['data'][1]
            while(len(x['data'][0])>50):
                data_ = list(x['data'][0].keys())[0]
                del x['data'][0][data_]
            for k in x['data'][0].keys():
                if isinstance(x['data'][0][k],str):
                    x['data'][0][k] = x['data'][0][k][:20].replace('"','')
            write('/html/body/div[1]/div/div[1]/table/tbody/tr[5]/td/textarea', json.dumps(x, ensure_ascii=False)) # 更改返回值
            click('/html/body/div[1]/div/h2[1]/a') # 点击添加输入参数
            write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[2]/input', parameter_name) # 写入参数名称
            write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[4]/input',suoyinshuoming) # 写入参数描述
            write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[8]/input', parameter_value) # 写入请求示例
            z = json.loads(return_value)
            for k in z['data'][0].keys():
                if isinstance(z['data'][0][k],str):
                    z['data'][0][k] = z['data'][0][k][:20].replace('"','')
            z = z['data'][0]
            i = 1
            y = json.loads(chucanmiaoshu)
            for k,v in z.items():
                click('/html/body/div[1]/div/h2[2]/a') # 点击添加输出参数
                write('//*[@id="outtable"]/tbody/tr[{}]/td[2]/input'.format(i), k)
                write('//*[@id="outtable"]/tbody/tr[{}]/td[4]/input'.format(i), y[k] if y[k] else k)
                write('//*[@id="outtable"]/tbody/tr[{}]/td[6]/input'.format(i), v)
                i+=1
            driver.execute_script("window.scrollBy(0,10)") # 下拉页面
            click('//*[@id="savebtn"]') # 点击保存
            WebDriverWait(driver, 10).until(EC.alert_is_present())
            x = driver.switch_to.alert
            re = x.text
            sleep()
            x.accept()
            return re
        return auto_submit(guishubumen, interface_name, return_value, parameter_name, parameter_value, interface_address, biaoming, suoyinshuoming, chucanmiaoshu)
    else: # 不能搜索到则上报接口
        click('/html/body/div[2]/div[2]/input[4]') # 点击服务上报
        click('//a[contains(string(), "HttpRestFul")]') # 点击http
        sleep()
        driver.switch_to.default_content() #切换主页面
        switch('//*[@id="iframepage"]')
        sleep()
        iframe = driver.find_element(By.XPATH, '//*[@id="layui-layer-iframe2"]')
        driver.switch_to.frame(iframe)
        click('//option[text()="{}"]'.format(guishubumen)) # 点击机构名称
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[2]/input', interface_name) # 写入接口名称
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[1]/td[3]/input', interface_address) #写入接口地址
        click('//*[@id="typeselect"]/option[1]') #点击接口类型
        click('/html/body/div[1]/div/div[1]/table/tbody/tr[2]/td[2]/span/select/option[2]') # 点击请求类型get
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[3]/td/input', biaoming) # 写入查询业务表
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[4]/td/textarea', interface_name) # 写入接口描述
        print(return_value)
        x = json.loads(return_value)
        while(len(x['data'])>1):
            del x['data'][1]
        while(len(x['data'][0])>50):
            data_ = list(x['data'][0].keys())[0]
            del x['data'][0][data_]
        for k in x['data'][0].keys():
            if isinstance(x['data'][0][k],str):
                x['data'][0][k] = x['data'][0][k][:20].replace('"','')
        write('/html/body/div[1]/div/div[1]/table/tbody/tr[5]/td/textarea', json.dumps(x, ensure_ascii=False)) # 更改返回值
        click('/html/body/div[1]/div/h2[1]/a') # 点击添加输入参数
        write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[2]/input', parameter_name) # 写入参数名称
        write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[4]/input',suoyinshuoming) # 写入参数描述
        write('/html/body/div[1]/div/div[2]/table/tbody/tr/td[8]/input', parameter_value) # 写入请求示例
        z = json.loads(return_value)
        for k in z['data'][0].keys():
            if isinstance(z['data'][0][k],str):
                z['data'][0][k] = z['data'][0][k][:20].replace('"','')
        z = z['data'][0]
        i = 1
        y = json.loads(chucanmiaoshu)
        for k,v in z.items():
            click('/html/body/div[1]/div/h2[2]/a') # 点击添加输出参数
            write('//*[@id="outtable"]/tbody/tr[{}]/td[2]/input'.format(i), k)
            write('//*[@id="outtable"]/tbody/tr[{}]/td[4]/input'.format(i), y[k] if y[k] else k)
            write('//*[@id="outtable"]/tbody/tr[{}]/td[6]/input'.format(i), v)
            i+=1
        driver.execute_script("window.scrollBy(0,10)") # 下拉页面
        click('//*[@id="savebtn"]') # 点击保存
        WebDriverWait(driver, 10).until(EC.alert_is_present())
        x = driver.switch_to.alert
        re = x.text
        sleep()
        x.accept()
        return re


def auto_unsubmit(interface_name):
    '''执行一条自动提交动作
    '''
    login_submit()
    sleep()
    click('/html/body/div[2]/div[1]/ul/li[7]/a')# 点击接口管理
    switch('/html/body/div[2]/div[2]/iframe') # 切换iframe
    click('/html/body/div[2]/div[1]/a[2]') # 点击地市上报接口
    write('/html/body/div[2]/div[2]/input[2]', interface_name) # 写入服务名称
    click('/html/body/div[2]/div[2]/input[3]') # 点击搜索
    sleep()
    if exist('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]'): # 可以搜索到
        # click('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[1]') # 点击查看详情
        # sleep()
        # switch('//*[@id="layui-layer-iframe1"]')
        # sleep()
        # click('//*[@id="savebtn"]') #点击更新
        # sleep()
        # sleep()
        # sleep()
        # sleep()
        # x = driver.switch_to.alert
        # sleep()
        # sleep()
        # x.accept()
        # sleep()
        # driver.switch_to.default_content() #切换主页面
        # click('/html/body/div[2]/div[1]/ul/li[7]/a')# 点击接口管理
        # switch('/html/body/div[2]/div[2]/iframe') # 切换iframe
        # click('/html/body/div[2]/div[1]/a[2]') # 点击地市上报接口
        # write('/html/body/div[2]/div[2]/input[2]', interface_name) # 写入服务名称
        # click('/html/body/div[2]/div[2]/input[3]') # 点击搜索
        click('/html/body/div[2]/div[3]/table/tbody/tr[2]/td[7]/a[2]') # 点击删除
        sleep()
        sleep()
        x = driver.switch_to.alert
        sleep()
        x.accept()
        sleep()

def submit():
    file_name = '1新设计接口清单接口.xlsx'
    df = pd.read_excel(file_name, converters={'appkey':str,'入参示例':str})
    for i in df.index:
        # if df.loc[i, '接口是否上报'] != '上报过程出错':
        #     continue
        if i!=77:
            continue
        print(i, df.loc[i, '接口'], end=' ')
        try:
            re = auto_submit(df.loc[i, '上报机构新'], df.loc[i, '接口'], df.loc[i, '代码测试输出'], df.loc[i, '索引'], df.loc[i, '入参示例'], df.loc[i, '接口地址'], df.loc[i, '表名'], df.loc[i, '索引说明'], df.loc[i, '出参字段描述'])
            df.loc[i, '接口是否上报']=re
            print(re)
        except Exception as e:
            print(e)
            df.loc[i, '接口是否上报']='上报过程出错'
            df.to_excel('result.xlsx')
            print('上报过程出错')
            # quit()
    df.to_excel(file_name.replace('.xlsx', '_上报结果.xlsx'))

def login_portal():
    login_resource()

def login_inquiry():
    driver.maximize_window()
    # 打开页面
    driver.get('http://10.64.202.213/inquiry/')
    # 填入用户名和密码
    write('//*[@id="i-username"]', 'platform_admin')
    write('//*[@id="i-password"]', '!QAZ2wsx')
    # 点击登录
    click('//*[@id="dzzw-igp-form-login"]/button')



def auto_resource_check(interface):
    def match(text):
        if '表中无数据存在' in text:
            return 0
        for i in range(text.count('<tr')):
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[1]'.format(i+1)), catalog)
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[2]'.format(i+1)), interface)
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[3]'.format(i+1)), organization)
            # print(get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[4]'.format(i+1)), '服务接口')
            if get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[2]'.format(i+1)) == interface \
            and get_text('//*[@id="table-apply"]/tbody/tr[{}]/td[4]'.format(i+1)) == '服务接口':
                return i+1
        return 0
    
    def get_appkey(n):
        click('//*[@id="table-apply_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[3]'.format(n)) # 点击查看授权
        sleep()
        sleep()
        b = get_text('//*[@id="secretInfo"]/table/tbody/tr[2]/td[2]') # 获取appkey
        c = get_text('//*[@id="servicePath"]') # 获取接口地址
        return b,c
    
    driver.get('http://10.64.202.213/portal') # 打开页面
    change_language()
    click('/html/body/div[1]/div[1]/div/div/a[2]') # 点击用户中心
    click('//*[@id="accordion"]/li[3]/a') # 点击我的申请
    click('//*[@id="pMyResourceApply"]') # 点击我的申请
    switch('//*[@id="screenIfram"]') # 切换iframe
    click('/html/body/div[1]/section/div/section/div/header/ul/li[3]/a') # 点击审核通过
    write('//*[@id="searchNameApply"]',interface) # 输入查询信息，接口名称
    click('//*[@id="search_data_apply"]') # 点击查询按钮
    sleep()
    # 精确选择筛选的按钮
    x = match(get_html('//*[@id="table-apply"]/tbody'))
    if x:
        result = get_appkey(x)
        return result[0], result[1], '资源审核已通过' 
    else:
        driver.switch_to.default_content() #切换主页面
        click('//*[@id="pApi"]/a') # 点击政府服务资源
        sleep()
        write('//*[@id="name_forsearch"]', interface) # 输入搜索内容
        click('//*[@id="select-border"]/ul/li[2]') # 点击接口
        click('//*[@id="keyword_search"]') # 点击搜索按钮
        sleep()
        if not exist('//a[@title="{}"]'.format(interface)):
            return '', '', '接口尚未发布'
        click('//a[@title="{}"]'.format(interface)) # 点击搜索到的第一个元素
        driver.switch_to.window(driver.window_handles[-1])  # driver切换至最新生产的页面
        sleep()
        sleep()
        click('/html/body/div[3]/div/div/div[1]/div[2]/ul/li[1]/a') # 点击资源申请
        click('//*[@id="userSystemInfo"]/div/div/span/button') # 点击选择
        sleep()
        sleep()
        click('//*[@id="df-58"]') # 点击宁德数据接口选择
        sleep()
        click('//*[@id="buttonusemodel"]') # 点击办事场景选择
        sleep()
        click('//*[@id="item-list"]/tbody/tr[2]/td[4]/input') # 点击复选框
        click('//*[@id="selectedItem"]') # 点击确定
        write('//*[@id="apply_basis"]','暂无') # 写入申请依据 暂无
        write('//*[@id="form_advance"]/div[7]/div[1]/div/div/input','50000')# 写入 50000
        write('//*[@id="form_advance"]/div[7]/div[2]/div/div/input', '50000') # 写入 50000
        write('//*[@id="form_advance"]/div[8]/div[1]/div/div/input','1095') # 写入 1095
        click('//*[@id="service_usetime"]/option[2]') # 点击全天
        click('//*[@id="pickfiles"]') # 点击选择文件
        sleep()
        sleep()
        pyautogui.write(os.getcwd()+r'\Word.docx')
        sleep()
        sleep()
        pyautogui.press('enter') # 敲击enter进行保存
        sleep()
        sleep()
        sleep()
        sleep()
        click('//*[@id="btn-info-botton"]') # 点击提交申请
        sleep()
        click('//*[@id="gotoUserCenter"]') # 点击用户中心
        click('//*[@id="accordion"]/li[2]/a') #点击申请审核
        click('//*[@id="pUserResourceAudit"]') # 点击资源申请审核
        switch('//*[@id="screenIfram"]')
        click('//*[@id="table-resApply_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[1]/td/a') #点击受理
        sleep()
        sleep()
        write('//*[@id="opinion"]', '通过') #写入通过
        click('//*[@id="resource-accept-botton"]') # 点击受理
        click('//*[@id="layui-layer1"]/div[3]/a[1]') # 点击确定
        longsleep()
        driver.close()
        driver.switch_to.window(driver.window_handles[0])
        return auto_resource_check(interface)
    
def portal():
    login_portal()
    df = pd.read_excel('分工.xlsx')
    try:
        for i in df.index:
            if pd.isnull(df.iloc[i]['appkey']):
                interface_code,appkey,interface_address,support_unit = auto_portal(df.iloc[i]['资源提供方'], df.iloc[i]['目录'], df.iloc[i]['库表']+'信息查询接口')
                df.loc[i,'接口编码'] = interface_code
                df.loc[i,'appkey'] = appkey
                df.loc[i,'rest接口'] = interface_address
                df.loc[i,'技术支持单位'] = support_unit
            print('{}. {} has done!'.format(i, df.iloc[i]['库表']))
    except Exception as e:
        print(e)
    finally:
        df.to_excel('result.xlsx')

def get_data_from_database():
    # 从数据库
    conn=pymysql.connect(host='10.64.202.214', port=3306, user='root', password='inspur135?!', db='dsp_cascade_ningde', charset='utf8')
    cursor=conn.cursor()
    df = pd.read_excel('全部接口.xlsx')
    for i in df.index:
        print(df.loc[i,'TABLENAME'])
        table = df.loc[i,'TABLENAME']
        # 获取字段名
        sql = 'show columns from {}'.format(table)
        rows = cursor.execute(sql)
        data = cursor.fetchall()
        df.loc[i,'数据库字段'] = str(data)
        sql = 'select * from {} LIMIT 1'.format(table)
        rows = cursor.execute(sql)
        data = cursor.fetchall()
        df.loc[i,'数据库数据示例'] = str(data)
    cursor.close()
    conn.close()
    df.to_excel('全部接口1.xlsx')

def exist_register(catalog_name):
    '''判断一个目录是否存在
    采用目录名完全匹配的方式，不匹配提供方
    '''
    driver.get('http://10.64.202.213/catalog/')
    click('//*[@id="nav-side"]/li[3]') # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[1]') # 点击目录编制
    switch('//*[@id="frame"]') # 切换frame
    write('//*[@id="cata_title"]', catalog_name) # 写入目录名称
    click('//*[@id="cata_status"]/option[7]') # 点击已发布
    click('//*[@id="search_data"]') # 点击查询
    sleep()
    x = get_html('//*[@id="list-table"]/tbody') # 获取表格内容
    def match():
        if '表中无数据存在' in x:
            return False
        for i in range(1, x.count('<tr')+1):
            # 匹配目录名
            if catalog_name == get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(i)):
                return True
        return False
    return match()

def auto_catalog_register(organization_name, system_name, catalog_name,ziduan):
    re = organization_name
    leixing = {
            'longtext':'长文本text',
            'tinytext':'长文本text',
            'datetime':'日期时间型T',
            'varchar':'字符串型C',
            'decimal':'浮点型F',
            'longblob':'二进制blob',
            'mediumtext':'长文本text',
            'char':'字符串型C',
            'double':'浮点型F',
            'int':'整型I',
            'timestamp':'日期时间型T',
            'blob':'二进制blob',
            'text':'长文本text',
            'date':'日期时间型T',
            'tinyint':'整型I',
            'bigint':'整型I',
            'float':'浮点型F',
    }
    driver.get('http://10.64.202.213/catalog/')
    click('//*[@id="nav-side"]/li[3]') # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[1]') # 点击目录编制
    switch('//*[@id="frame"]') # 切换frame
    sleep()
    click('//*[@id="#/3070194903"]/i') # 点击 + ，展开部门资源目录
    sleep()
    click('//*[@id="#/3070194903/307019490303"]/i') # 点击+，展开行政部门目录
    sleep()
    sleep()
    if not exist('//a[text()="{}"]'.format(organization_name)):
        organization_name = '市信息中心'
        re = '市信息中心'
    target = driver.find_element(By.XPATH, '//a[text()="{}"]'.format(organization_name))
    sleep()
    driver.execute_script("arguments[0].scrollIntoView();", target)
    sleep()
    click('//a[text()="{}"]'.format(organization_name)) # 选择市信息中心
    sleep()
    sleep()
    target = driver.find_element(By.XPATH, '//*[@id="table-toolbar"]/div[9]/button[4]')
    sleep()
    sleep()
    driver.execute_script("arguments[0].scrollIntoView();", target)
    sleep()
    click('//*[@id="table-toolbar"]/div[9]/button[4]') # 点击添加目录
    write('//*[@id="indiv"]/div[2]/div/input', catalog_name) # 写入目录名
    click('//*[@id="selectFromSystemDiv"]/span/button') # 点击选择系统
    sleep()
    write('//*[@id="from_system_search_keywords"]', system_name) # 输入系统名
    click('//*[@id="from_system_search_data"]') # 点击查询
    sleep()
    click('//*[@id="dataCenterTable"]/tbody/tr/td[1]/input') # 点击选择按钮
    click('//*[@id="selectFromSystem"]/div/div/div[2]/button[1]') # 点击确定
    click('//*[@id="catalog_shared_type"]/option[2]') # 共享类型选择有条件共享
    write('//*[@id="catalog_shared_condition"]', '经领导审批后共享') # 写入共享条件 经领导审批后共享
    click('//*[@id="catalog_open_type"]/option[3]') # 开放类型选择 不开放
    write('//*[@id="description"]', catalog_name) # 写入来源系统
    click('//*[@id="steps-uid-0"]/div[3]/ul/li[2]') # 点击下一步
    sleep()
    sleep()
    # ==================================================================
    # 解析字段
    for i in ziduan.split(','):
        j = i.split(';')
        click('//*[@id="steps-uid-0-p-1"]/div/div[1]/div/a[2]') # 点击添加
        sleep()
        write('//*[@id="name_cn"]',j[0]) # 写入信息项名称
        click('//*[@id="data_format"]/option[text()="{}"]'.format(leixing[j[1]])) # 点击类型
        if j[2]:
            write('//*[@id="length"]', j[2]) #写入长度
        click('//*[@id="column_submit"]') # 点击确定
    click('//*[@id="steps-uid-0"]/div[3]/ul/li[4]/a') # 点击提交
    sleep()
    # 目录审核
    driver.switch_to.default_content() #切换主页面
    click('//*[@id="nav-side"]/li[3]') # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[4]') # 点击目录审核
    sleep()
    switch('//*[@id="frame"]') # 切换frame
    sleep()
    click('//*[@id="list-approve_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') # 点击审核
    click('//*[@id="submitDIV"]/div/button[1]') #点击审核通过
    click('//*[@id="layui-layer1"]/div[3]/a[1]') # 点击确定
    
    # 目录发布
    driver.switch_to.default_content() #切换主页面
    click('//*[@id="nav-side"]/li[3]') # 点击目录编制与报送
    click('//*[@id="nav-side"]/li[3]/ul/li[3]') # 点击目录发布
    switch('//*[@id="frame"]') # 切换frame
    sleep()
    click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') # 点击发布
    click('//*[@id="submitDIV"]/div/button[1]') #点击发布
    click('//*[@id="layui-layer1"]/div[3]/a[1]') # 点击确定
    state = '目录已存在'
    return re

def return_value_test(interface_address, input_par_name, input_par_value, appkey):
    import requests
    if pd.isnull(interface_address) or pd.isnull(input_par_name) or pd.isnull(input_par_value) or pd.isnull(appkey):
        return '信息不全,无法测试', ''
    url = "{}?{}={}".format(interface_address, input_par_name, input_par_value)
    payload = {}
    headers = {'appkey': appkey}
    response = requests.request("GET", url, headers=headers, data=payload)
    x = json.loads(response.text)
    if 'code' in x and 'data' in x and x['code'] == 200 and x['data'] and input_par_value in response.text:
        return '测试通过', response.text
    else:
        return '测试不通过', response.text


def catalog():
    df = pd.read_excel('全部数据.xlsx', converters={'appkey':str})
    for i in df.index:
        print(i, df.loc[i, '接口'])
        if df.loc[i, '目录是否存在'] == '目录已存在':
            continue
        if df.loc[i, '页面测试是否通过'] != '数据库填报错误':
            continue
        if pd.isnull(df.loc[i, '目录']) or df.loc[i, '目录']=='':
            df.loc[i, '目录是否存在'] = '目录为空'
            continue
        if pd.isnull(df.loc[i, '字段']) or df.loc[i, '字段']=='':
            df.loc[i, '目录是否存在'] = '字段为空'
            continue
        if pd.isnull(df.loc[i, '机构']) or df.loc[i, '机构']=='':
            df.loc[i, '目录是否存在'] = '机构为空'
            continue
        try:
            if not exist_register(df.loc[i, '目录']):
                re = auto_catalog_register(df.loc[i, '机构'], '宁德市政务大数据平台', df.loc[i, '目录'], df.loc[i, '字段'])
                df.loc[i, '机构'] = re
            else:
                df.loc[i, '目录是否存在'] = '目录已存在'
        except:
            df.loc[i, '目录是否存在'] = '目录注册过程出错'
            # df.to_excel('quanbushuju.xlsx')
            continue
        df.loc[i, '目录是否存在'] = '目录已存在'
    df.to_excel('全部数据.xlsx')

def dt():
    df = pd.read_excel('接口第一版-测试未通过.xlsx', converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '页面测试是否通过'] != '库表重新注册':
            continue
        if pd.isnull(df.loc[i, '目录']) or df.loc[i, '目录']=='':
            df.loc[i, '库表是否存在'] = '目录为空'
            continue
        if pd.isnull(df.loc[i, '数据库名']) or df.loc[i, '数据库名']=='':
            df.loc[i, '库表是否存在'] = '数据库名为空'
            continue
        if pd.isnull(df.loc[i, '表名']) or df.loc[i, '表名']=='':
            df.loc[i, '库表是否存在'] = '表名为空'
            continue
        if pd.isnull(df.loc[i, '库表']) or df.loc[i, '库表']=='':
            df.loc[i, '库表是否存在'] = '库表为空'
            continue
        print(i, df.loc[i, '库表'])
        try:
            if not exist_dt(df.loc[i, '库表']):
                auto_dt_register('宁德市政务大数据平台', df.loc[i, '目录'], df.loc[i, '库表'], df.loc[i, '数据库名'], df.loc[i, '表名'])
            else:
                df.loc[i, '库表是否存在'] = '库表已存在'

        except Exception as e:
            df.loc[i, '库表是否存在'] = '库表注册过程出错'
            print(e)
            df.to_excel('quanbushuju.xlsx')
            print('库表注册过程出错')
            quit()
            continue
        df.loc[i, '库表是否存在'] = '库表已存在'
    df.to_excel('quanbushuju.xlsx')


def interface():
    file_name = '1新设计接口清单接口正在页面测试.xlsx'
    df = pd.read_excel(file_name, converters={'appkey':str})
    for i in df.index:
        if pd.isnull(df.loc[i, '目录']) or df.loc[i, '目录']=='':
            df.loc[i, '接口是否存在'] = '目录为空'
            continue
        if pd.isnull(df.loc[i, '索引']) or df.loc[i, '索引']=='':
            df.loc[i, '接口是否存在'] = '索引为空'
            continue
        if pd.isnull(df.loc[i, '表名']) or df.loc[i, '表名']=='':
            df.loc[i, '接口是否存在'] = '表名为空'
            continue
        if pd.isnull(df.loc[i, '库表']) or df.loc[i, '库表']=='':
            df.loc[i, '接口是否存在'] = '库表为空'
            continue
        if pd.isnull(df.loc[i, '接口']) or df.loc[i, '接口']=='':
            df.loc[i, '接口是否存在'] = '接口为空'
            continue
        print('接口注册', i, df.loc[i, '接口'])
        try:
            if not exist_interface(df.loc[i, '接口']):
                re = auto_interface_register('宁德市政务大数据平台', df.loc[i, '目录'], df.loc[i, '库表'], df.loc[i, '表名'], df.loc[i, '索引'], df.loc[i, '接口'], df.loc[i, '接口地址英文后缀'])
                print(re)
                df.loc[i, '接口是否存在'] = re
            else:
                df.loc[i, '接口是否存在'] = '接口已存在'
        except Exception as e:
            print(e)
            df.loc[i, '接口是否存在'] = '接口注册过程出错'
            df.to_excel(file_name.replace('.xlsx', '_接口注册.xlsx'))
            # quit()
    df.to_excel(file_name.replace('.xlsx', '_接口注册.xlsx'))


def unsubmit():
    df = pd.read_excel('全部数据-删上报.xlsx', converters={'appkey':str})
    for i in df.index:
        print('接口删除', i, df.loc[i, '接口'])
        if df.loc[i, '接口是否已删除'] == '接口已删除':
            continue
        try:
            auto_unsubmit(df.loc[i, '接口'])
        except:
            df.loc[i, '接口是否已删除'] = '接口删除过程出错'
            continue
        df.loc[i, '接口是否已删除'] = '接口已删除'
    df.to_excel('全部数据-删上报123.xlsx')


def web_test():
    file_name = '1新设计接口清单接口正在页面测试.xlsx'
    df = pd.read_excel(file_name, converters={'appkey':str,'入参示例':str})
    for i in df.index:
        # if df.loc[i, '页面测试是否通过'] != '库表重新注册':
        #     continue
        # if df.loc[i, '页面测试是否通过'] == '测试通过':
        #     continue
        # if df.loc[i, '页面测试是否通过'] == '等待时间过长':
        #     continue
        # if df.loc[i, '页面测试是否通过'] == '待测试状态中无此接口':
        #     continue
        if pd.isnull(df.loc[i, '入参示例']) or df.loc[i, '入参示例']=='':
            df.loc[i, '页面测试是否通过'] = '入参示例为空'
            continue
        if pd.isnull(df.loc[i, '接口']) or df.loc[i, '接口']=='':
            df.loc[i, '页面测试是否通过'] = '接口为空'
            continue
        print(i, df.loc[i, '接口'])
        try:
            re = auto_test(df.loc[i, '接口'], df.loc[i, '入参示例'])
            if re[0] == '待测试状态中无此接口':
                df.loc[i, '页面测试是否通过'] = re[0]
            else:
                df.loc[i, '页面测试是否通过'] = re[0]
                df.loc[i, '页面测试输出'] = re[1]
                print(re[0])
        except:
            df.loc[i, '页面测试是否通过'] = '页面测试过程出错'
            print('页面测试过程出错')
            df.to_excel(file_name.replace('.xlsx', '_页面测试.xlsx'))
            # quit()
    df.to_excel(file_name.replace('.xlsx', '_页面测试.xlsx'))

def check():
    file_name = '1新设计接口清单接口.xlsx'
    df = pd.read_excel(file_name, converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '资源审核是否通过'] == '资源审核已通过':
            continue
        print(i, df.loc[i, '接口'])
        if pd.isnull(df.loc[i, '接口']) or df.loc[i, '接口']=='':
            df.loc[i, '资源审核是否通过'] = '接口为空'
            continue
        try:
            result = auto_resource_check(df.loc[i, '接口'])
            df.loc[i, 'appkey'] = result[0]
            df.loc[i, '接口地址'] = result[1]
            df.loc[i, '资源审核是否通过'] = result[2]
        except:
            df.loc[i, '资源审核是否通过'] = '审核过程出错'
            df.to_excel(file_name.replace('.xlsx', '_资源审核结果.xlsx'))
            continue
    df.to_excel(file_name.replace('.xlsx', '_资源审核结果.xlsx'))

def find_index():
    global driver
    df = pd.read_excel('全部数据.xlsx', converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '代码测试是否通过']=='测试不通过':
            print(i, df.loc[i, '接口'])
            driver.get('http://10.64.202.213/resource')
            driver.switch_to.default_content() #切换主页面
            click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
            click('//*[@id="nav-side"]/li[8]/ul/li[2]/a') # 点击数据服务维护
            switch('//*[@id="frame"]') # 切换frame
            # click('/html/body/div[1]/div/div/section/div/form/div/div[2]/section[1]/div/div[1]/div/div/span/button') # 点击选择库表资源
            write('//*[@id="search_resource_title_code"]',df.loc[i, '接口']) # 写入资源名称
            click('//*[@id="search_resource"]') # 点击查询
            sleep()
            click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[1]/td/a[1]') # 点击查看
            sleep()
            click('//*[@id="tab_1_1"]/div[1]/ul/li[2]/a') # 点击数据服务信息
            df.loc[i, '索引'] = get_text('//*[@id="tab_dataService_2"]/div/div[2]/div/table/tbody/tr/td[1]') # 获取索引名
    df.to_excel('quanbushuju.xlsx')

def find_index_value():
    global driver
    df = pd.read_excel('全部数据.xlsx', converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '代码测试是否通过']=='测试不通过':
            try:
                print(i, df.loc[i, '接口'])
                host = df.loc[i, '数据库'].strip().split('/')[0]
                db = df.loc[i, '数据库'].strip().split('/')[1]
                table = df.loc[i, '表名'].strip()
                print(host, db, table)
                if host == '10.64.202.214':
                    conn=pymysql.connect(host=host, port=3306, user='root', password='inspur135?!', db=db, charset='utf8')
                else:
                    conn=pymysql.connect(host=host, port=3306, user='root', password='123456a?', db=db, charset='utf8')
                cursor=conn.cursor()
                sql = 'select {} from {} LIMIT 1'.format(df.loc[i, '索引'].strip(), table)
                rows = cursor.execute(sql)
                data = cursor.fetchall()
                # df.loc[i,'数据库数据示例'] = str(data)
                print(str(data[0][0]))
                cursor.close()
                conn.close()
                df.loc[i,'入参示例'] = str(data[0][0])
            except Exception as e:
                print(e)
                continue
    df.to_excel('quanbushuju.xlsx')




def auto_huanshujuku(interface_name):
    def match():
        x = get_html('//*[@id="list-table"]/tbody') # 获取内容
        if '表中无数据存在' in x:
            return 0
        for i in range(1, x.count('<tr')+1):
            # 匹配接口名
            if interface_name == get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)):
                return i
        return 0
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
    click('//*[@id="nav-side"]/li[8]/ul/li[2]') # 点击数据服务维护
    switch('//*[@id="frame"]') # 切换frame
    write('//*[@id="search_resource_title_code"]',interface_name)  #填入资源名称
    click('//*[@id="search_resource_status"]/option[2]') # 点击草稿
    click('//*[@id="search_resource"]') # 点击查询
    sleep()
    n = match()
    if n:
        click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr["{}"]/td/a[2]'.format(n))

    


def huanshujuku():
    df = pd.read_excel('全部数据.xlsx', converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '页面测试是否通过']=='数据库填报错误':
            auto_huanshujuku(df.loc[i,'接口'])
        


def get_chucan(jiekoudizhi,suoyin,rucanshili,appkey):
    url = "{}?{}={}".format(jiekoudizhi,suoyin,rucanshili)
    payload = {}
    headers = {'appkey': str(appkey)}
    response = requests.request("GET", url, headers=headers, data=payload)
    return response.text

def auto_interface_info_check(interface_name):
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
    click('//*[@id="nav-side"]/li[8]/ul/li[2]') # 点击数据服务维护
    switch('//*[@id="frame"]') # 切换frame
    write('//*[@id="search_resource_title_code"]',interface_name)  #填入资源名称
    click('//*[@id="search_resource_status"]/option[5]') # 点击已发布
    click('//*[@id="search_resource"]') # 点击查询
    sleep()
    x = get_html('//*[@id="list-table"]/tbody') # 获取内容
    def match():
        if '表中无数据存在' in x:
            return 0
        for i in range(1, x.count('<tr')+1):
            # 匹配接口名
            if interface_name == get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)):
                return i
        return 0
    n = match()
    if n:
        click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[1]'.format(n))
        sleep()
        click('//*[@id="tab_1_1"]/div[1]/ul/li[2]/a') # 点击数据信息服务
        re = get_text('//*[@id="tab_dataService_2"]/div/div[2]/div/table/tbody/tr/td[1]') # 获取参数名
        return '已发布', re
    else:
        driver.get('http://10.64.202.213/resource')
        driver.switch_to.default_content() #切换主页面
        click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
        click('//*[@id="nav-side"]/li[8]/ul/li[2]') # 点击数据服务维护
        switch('//*[@id="frame"]') # 切换frame
        write('//*[@id="search_resource_title_code"]',interface_name)  #填入资源名称
        click('//*[@id="search_resource_status"]/option[8]') # 点击待测试
        click('//*[@id="search_resource"]') # 点击查询
        sleep()
        x = get_html('//*[@id="list-table"]/tbody') # 获取内容
        def match():
            if '表中无数据存在' in x:
                return 0
            for i in range(1, x.count('<tr')+1):
                # 匹配接口名
                if interface_name == get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)):
                    return i
            return 0
        n = match()
        if n:
            click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[1]'.format(n))
            sleep()
            click('//*[@id="tab_1_1"]/div[1]/ul/li[2]/a') # 点击数据信息服务
            re = get_text('//*[@id="tab_dataService_2"]/div/div[2]/div/table/tbody/tr/td[1]') # 获取参数名
            return '已发布', re
        else:
            return '找不到接口', ''


def interface_info_check():
    file_name = '资源审核未通过.xlsx'
    def match():
        x = get_html('//*[@id="list-table"]/tbody') # 获取内容
        if '表中无数据存在' in x:
            return 0
        for i in range(1, x.count('<tr')+1):
            # 匹配接口名
            if interface_name == get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]/span'.format(i)):
                return i
        return 0
    sleep()
    sleep()
    driver.get('http://10.64.202.213/resource')
    sleep()
    sleep()
    driver.switch_to.default_content() #切换主页面
    sleep()
    sleep()
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[8]') # 点击数据服务
    sleep()
    click('//*[@id="nav-side"]/li[8]/ul/li[2]') # 点击数据服务维护
    sleep()
    switch('//*[@id="frame"]') # 切换frame
    df = pd.read_excel(file_name, converters={'appkey':str})
    for i in df.index:
        if df.loc[i,'接口状态'] == '已发布':
            continue
        print(i)
        interface_name = df.loc[i, '接口']
        write('//*[@id="search_resource_title_code"]',interface_name)  #填入资源名称
        click('//*[@id="search_resource"]') # 点击查询
        WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, '//*[@id="list-table"]/tbody')))
        sleep()
        n = match()
        if n:
            df.loc[i,'接口状态'] = get_text('//*[@id="list-table"]/tbody/tr[{}]/td[7]'.format(n))
        else:
            df.loc[i,'接口状态'] = '接口不存在'
        print(df.loc[i,'接口状态'])
    df.to_excel(file_name.replace('.xlsx', '_接口状态结果.xlsx'))


def auto_xinyonghuhuanyuan(dt_name):
    def match():
        x = get_html('//*[@id="list-table"]/tbody')
        if '表中无数据存在' in x:
            return 0
        for j in range(x.count('<tr')):
            if get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(j+1))==dt_name:
                return j+1
        return 0
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[4]/a") ## 点击‘资源维护’
    ## 切换iframe
    switch('//*[@id="frame"]')
    ## 输入查询内容
    write('html/body/div[1]/section/div/form/div[1]/input', dt_name)
    ## 点击库表
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")
    ## 点击查询
    click("/html/body/div[1]/section/div/form/div[6]/button[1]")
    sleep()
    n = match()
    if not n:
        dt_name = dt_name+'新'
        n = match()
    if n:
        click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[2]'.format(n))  # 点击变更
        sleep()
        click('//*[@id="steps-uid-0"]/div[3]/ul/li[2]/a') # 点击下一步
        sleep()
        click('//*[@id="steps-uid-0-p-1"]/div/div[1]/div/div/span/button') # 点击换源
        sleep()
        write('//*[@id="changeDatasourceKeyword"]', '信用库')
        sleep()
        click('//*[@id="changeDatasourceSearch"]') # 点击查询
        sleep()
        click('//*[@id="changeDatasourceTable"]/tbody/tr[2]/td[1]/input') # 选择第二个
        sleep()
        click('//*[@id="changeDatasourceConfirm"]') # 点击确定
        sleep()
        click('//*[@id="steps-uid-0"]/div[3]/ul/li[4]/a') # 点击保存并提交
        driver.switch_to.default_content() #转到主页面
        sleep()
        click('/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/a') #点击资源审核
        click('/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/ul/li[1]/a') #点击资源注册审核
        switch('/html/body/div[3]/div[2]/div/div[3]/iframe') #转到frame
        write('/html/body/div[1]/section/div/form/div[1]/input',dt_name) #写入库表名称
        # click('/html/body/div[1]/section/div/form/div[4]/select') #点击资源类型
        click('/html/body/div[1]/section/div/form/div[4]/select/option[2]') #点击库表
        click('/html/body/div[1]/section/div/form/div[5]/button[1]') #点击查询
        sleep()
        click('/html/body/div[1]/section/div/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') #点击审核
        sleep()
        click('/html/body/div/section/div/div/div[2]/div[1]/form/div/div/button[1]') #点击审核通过
        click('/html/body/div[3]/div[3]/a[1]') #点击确定
        sleep()
        sleep()
        driver.switch_to.default_content() #切换主页面
        click('/html/body/div[3]/div[1]/div/div[2]/ul/li[6]/a') #点击资源发布
        switch('/html/body/div[3]/div[2]/div/div[3]/iframe') #切换frame
        write('/html/body/div[1]/section/div/form/div[1]/input',dt_name) #输入资源名称
        click('/html/body/div[1]/section/div/form/div[4]/select/option[2]') #点击库表
        click('//button[@id="search_resource"]') #点击查询
        sleep()
        click('/html/body/div[1]/section/div/div/div[1]/div/div[3]/div[2]/div/table/tbody/tr/td/a') #点击发布
        driver.switch_to.default_content() #切换主页面
        click('//a[text()="确定"]') #点击确定

def xinyonghuhuanyuan():
    df = pd.read_excel('接口第一版-测试未通过.xlsx', converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '页面测试是否通过'] != '数据库填报错误':
            continue
        if df.loc[i, '数据库名'] != '信用库':
            continue
        print(i, df.loc[i, '接口'])
        try:
            auto_xinyonghuhuanyuan(df.loc[i, '库表'])
            df.loc[i, '库表是否存在'] = '库表已更新'
        except:
            continue
    df.to_excel('接口第一版-测试未通过qqqq.xlsx')

def auto_shenhetongguo():
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #转到主页面
    sleep()
    sleep()
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/a') #点击资源审核
    sleep()
    click('/html/body/div[3]/div[1]/div/div[2]/ul/li[5]/ul/li[1]/a') #点击资源注册审核
    sleep()
    switch('/html/body/div[3]/div[2]/div/div[3]/iframe') #转到frame
    while True:
        x = get_text('//*[@id="list-table"]/tbody/tr[1]/td[5]')
        if not x.startswith('2023-06-16'):
            break
        click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[1]/td/a') #点击审核
        sleep()
        click('//*[@id="submitDIV"]/div/button[1]') #点击审核通过
        sleep()
        click('/html/body/div[3]/div[3]/a[1]') #点击确定
        # //*[@id="layui-layer1"]/div[3]/a[1]
        sleep()


def run():
    # 登录
    login_inquiry()
    df = pd.read_excel('全部数据.xlsx', converters={'appkey':str,'入参示例':str})
    # 读取数据
    for i in df.index:
        try:
            if df.loc[i, '接口是否上报']=='完成':
                continue
            if df.loc[i, '接口是否上报']=='跳过':
                continue
            if (not pd.isnull(df.loc[i, '接口是否上报'])) and '上报成功！' in df.loc[i, '接口是否上报']:
                continue
            print('序号', i, df.loc[i, '接口'])
            # 接口注册
            print('接口注册……', end = ' ')
            if df.loc[i, '接口是否存在'] == '接口已存在':
                print('接口已存在')
            else:
                if not exist_interface(df.loc[i, '接口']):
                    re = auto_interface_register('宁德市政务大数据平台', df.loc[i, '目录'], df.loc[i, '库表'], df.loc[i, '表名'], df.loc[i, '索引'], df.loc[i, '接口'], df.loc[i, '接口地址英文后缀'])
                    df.loc[i, '接口英文名'] = re
                    df.loc[i, '接口是否存在'] = '接口已存在'
                else:
                    df.loc[i, '接口是否存在'] = '接口已存在'
                print('接口已存在')
            # 接口测试
            print('接口页面测试……', end = ' ')
            if df.loc[i, '页面测试是否通过'] == '测试通过':
                print('测试通过')
            else:
                re = auto_test(df.loc[i, '接口'], df.loc[i, '入参示例'])
                if re[0] == '测试不通过':
                    print(re[0])
                    print('入参示例：',df.loc[i, '入参示例'])
                    print(re[1])
                    continue
                elif re[0] == '待测试状态中无此接口':
                    print('待测试状态中无此接口')
                    continue
                elif re[0] == '测试通过':
                    print('测试通过')
                    df.loc[i, '页面测试是否通过'] = re[0]
                    df.loc[i, '页面测试输出'] = re[1]
                    print(re[1])
                elif re[0] == '跳过':
                    df.loc[i, '接口是否上报']='跳过'
                    continue
            # 资源申请与审核
            print('资源申请与审核……', end = ' ')
            if df.loc[i, '资源审核是否通过'] == '资源审核已通过':
                print('资源审核已通过')
            else:
                if pd.isnull(df.loc[i, '接口']) or df.loc[i, '接口']=='':
                    df.loc[i, '资源审核是否通过'] = '接口为空'
                    continue
                result = auto_resource_check(df.loc[i, '接口'])
                df.loc[i, 'appkey'] = result[0]
                df.loc[i, '接口地址'] = result[1]
                df.loc[i, '资源审核是否通过'] = result[2]
                if result[2] == '接口尚未发布':
                    print('接口尚未发布')
                    quit()
                else:
                    print(result[2])
            # 接口上报
            print('接口上报……', end = ' ')
            if df.loc[i, '资源审核是否通过'] == '资源审核已通过' and pd.isnull(df.loc[i, '页面测试输出']):
                df.loc[i, '页面测试输出'] = get_chucan(df.loc[i, '接口地址'], df.loc[i, '索引'], df.loc[i, '入参示例'],df.loc[i, 'appkey'])
            if df.loc[i, '接口是否上报'] == '完成':
                print('上报完成')
            else:
                re = auto_submit(df.loc[i, '归属部门'], df.loc[i, '接口'], df.loc[i, '页面测试输出'], df.loc[i, '索引'], df.loc[i, '入参示例'], df.loc[i, '接口地址'], df.loc[i, '表名'], df.loc[i, '索引说明'], df.loc[i, '出参字段描述'])
                df.loc[i, '接口是否上报']=re
                print('上报完成')
        except Exception as e:
            print(e)
            df.to_excel('全部数据.xlsx')
            # quit()
            continue
    df.to_excel('全部数据.xlsx')

def auto_get_database_info(database_name):
    '''
    根据库表名来获取数据库的真实地址
    用于上报过程中,字段描述和返回值不匹配的更正
    '''
    driver.get('http://10.64.202.213/resource')
    driver.switch_to.default_content() #切换主页面
    click("/html/body/div[3]/div[1]/div/div[2]/ul/li[4]/a") ## 点击‘资源维护’
    ## 切换iframe
    switch('//*[@id="frame"]')
    ## 输入查询内容
    write('html/body/div[1]/section/div/form/div[1]/input', database_name)
    ## 点击库表
    click("/html/body/div[1]/section/div/form/div[4]/select/option[2]")
    ## 点击查询
    click("/html/body/div[1]/section/div/form/div[6]/button[1]")
    sleep()
    x = get_html('//*[@id="list-table"]/tbody')
    def match():
        if '表中无数据存在' in x:
            return 0
        for j in range(x.count('<tr')):
            if get_text('//*[@id="list-table"]/tbody/tr[{}]/td[2]'.format(j+1))==database_name:
                return j+1
        return 0
    n = match()
    if n:
        click('//*[@id="list-table_wrapper"]/div[1]/div/div[3]/div[2]/div/table/tbody/tr[{}]/td/a[1]'.format(n)) # 点击查看
        sleep()
        shujubiaomingcheng = get_html('//*[@id="tab_table_detail"]/div[1]/div/div[1]').strip()
        shujubiaomingcheng = shujubiaomingcheng[shujubiaomingcheng.find('：')+1:][:-6].strip()
        print(shujubiaomingcheng)
        shujukumingcheng = get_html('//*[@id="tab_table_detail"]/div[1]/div/div[2]').strip()
        shujukumingcheng = shujukumingcheng[shujukumingcheng.find('：')+1:][:-6].strip()
        shujukudizhi = get_html('//*[@id="tab_table_detail"]/div[1]/div/div[3]').strip()
        shujukudizhi = shujukudizhi[shujukudizhi.find('：')+1:][:-6].strip()
        return shujukudizhi+'/'+shujukumingcheng, shujubiaomingcheng


def get_database_info():
    file_name = '4代码测试通过294.xlsx'
    df = pd.read_excel(file_name, converters={'appkey':str})
    for i in df.index:
        if df.loc[i, '接口是否上报'] != '数据内容不匹配':
            continue
        print(i, df.loc[i, '接口'])
        try:
            re = auto_get_database_info(df.loc[i, '库表'])
            df.loc[i,'数据库'] = re[0]
            df.loc[i,'表名'] = re[1]
            print(re)
            quit()
        except:
            continue
    df.to_excel(file_name.replace('.xlsx', '_数据库内容更新.xlsx'))

if __name__ == '__main__':    
    driver = webdriver.Chrome()
    login_inquiry()
    # interface_info_check()
    # interface()
    # dt()
    # web_test()
    # check()
    submit()
    # get_database_info()
    driver.quit()